<?php
/**
 * polreg.php
 * @author ranuandra
 *
 * 10 Agu 2011 14:25:07
 *
 * 
 */

class Model_Polreg extends ORM
{
			protected $_belongs_to = array(
									'customer' 	=> array('model' => 'customer'),
									'type'		=> array('model' => 'type'),
									'upload'	=> array('model' => 'upload'),
									'area'		=> array('model' => 'area'),
								);
								
			public function transaksi($xls, $upid)
			{
				$datas = array();
				//$orpolreg = ORM::factory('polreg'); //polreg
/*				$orarea = ORM::factory('area'); // area
				$ormodel = ORM::factory('model'); //model
				$ormerk = ORM::factory('merk'); //merk
				$ortype = ORM::factory('type'); //type
				$orcustomer = ORM::factory('customer'); //customer*/
				$db = Database::instance();
				$db->begin();
				try 
				{			
					foreach($xls as $xl)
					{
						if($xl['nama']!="")
						{
							//master area
						   if(trim($xl['area'])!="")
							{
								//$dtarea = ;
	
								if(!ORM::factory('area')->where('area', '=', $xl['area'])->find()->id)
								{
									$result = DB::insert('areas', array('area'))->values(array($xl['area']))->execute();
									//$orarea->area = $xl['area'];
									//$orarea->save();
									$areaid = $result[0];
									
								}
								else 
								{
									$areaid = ORM::factory('area')->where('area', '=', $xl['area'])->find()->id;
								}
							}
	
							//cek master model
							if(trim($xl['model'])!="")
							{
								//$dtmodel = ORM::factory('model')->where('model','=', $xl['area'])->find();
								
								if(!ORM::factory('model')->where('model','=', $xl['model'])->find()->id)
								{								
									//$ormodel->model = $xl['area'];
									//$ormodel->save();
									$result = DB::insert('models', array('model'))->values(array($xl['model']))->execute();
									$modelid= $result[0];
								}
								else
								{
									$modelid = ORM::factory('model')->where('model','=', $xl['model'])->find()->id;
								}
							}
							
							//cek master merk
							if(trim($xl['merk'])!="")
							{
								//$dtmerk = ORM::factory('merk')->where('merk', '=', $xl['merk'])->find();
								//$dtmerk = $ormerk->cek_data($merk);
								if(!ORM::factory('merk')->where('merk', '=', $xl['merk'])->find()->id)
								{
									//$ormerk->merk = $xl['merk'];
									//$ormerk->save();
																
									//$merkid = $ormerk->id;
									$result = DB::insert('merks', array('merk'))->values(array($xl['merk']))->execute();
									$merkid= $result[0];
								}
								else 
								{
									$merkid = ORM::factory('merk')->where('merk', '=', $xl['merk'])->find()->id;
								}
							}
							
							//cek master type
							if(trim($xl['type'])!="")
							{
								//echo $i;
								//$dttype = ORM::factory('type')->where('type', '=', $xl['type'])->where('merk_id', '=', $merkid)->where('model_id', '=', $modelid)->find();
								
								if(!ORM::factory('type')->where('type', '=', $xl['type'])->where('merk_id', '=', $merkid)->where('model_id', '=', $modelid)->find()->id)
								{
									
									//$ortype->type = $xl['type'];
									//$ortype->merk_id = $merkid;
									//$ortype->model_id = $modelid;
									//$ortype->save();
									
									//$typeid = $ortype->id;
									$result = DB::insert('types', array('type', 'merk_id', 'model_id'))->values(array($xl['type'], $merkid, $modelid))->execute();
									$typeid= $result[0];
								}
								else
								{
									$typeid = ORM::factory('type')->where('type', '=', $xl['type'])->where('merk_id', '=', $merkid)->where('model_id', '=', $modelid)->find()->id;
								}
							}
							
							//cek master customer
							//$dtcustomer = ORM::factory('customer')->where('nama', '=', $xl['nama'])->where('alamat', '=', $xl['alamat'])->where('area_id', '=', $areaid)->find();
							if(!ORM::factory('customer')->where('nama', '=', $xl['nama'])->where('alamat', '=', $xl['alamat'])->where('area_id', '=', $areaid)->find()->id)
							{
								
								//$orcustomer->nama = $xl['nama'];
								//$orcustomer->alamat = $xl['alamat'];
								//$orcustomer->area_id = $areaid;
								//$orcustomer->save();
								
								//$customerid = $orcustomer->id;
									$result = DB::insert('customers', array('nama', 'alamat', 'area_id'))->values(array($xl['nama'], $xl['alamat'], $areaid))->execute();
									$customerid= $result[0];
							}
							else 
							{
								$customerid = ORM::factory('customer')->where('nama', '=', $xl['nama'])->where('alamat', '=', $xl['alamat'])->where('area_id', '=', $areaid)->find()->id;
							}

							//cek polreg
							//$dtpolreg = ORM::factory('polreg')->where('noka', '=', $noka)->where('nosin', '=', $nosin)->find();
							//$dtpolreg = $orpolreg->where('noka', '=', $noka)->where('nosin', '=', $nosin)->find();
								
							//$dtpolreg = $orpolreg->cek_data($nosin, $noka);
								
/*							if(!ORM::factory('polreg')->where('noka', '=', $xl['noka'])->where('nosin', '=', $xl['nosin'])->find()->id)
							{
							$orpolreg->customer_id = $customerid;
								//$orpolreg->model_id = $modelid;
								//$orpolreg->merk_id = $merkid;
								$orpolreg->type_id = $typeid;
								$orpolreg->noka = $noka;
								$orpolreg->nosin = $nosin;
								$orpolreg->tgl_daftar = $tgl;
								$orpolreg->upload_id = $uploadid;
								$orpolreg->area_id = $areaid;
								$orpolreg->thn_buat = $thn;
									
								$orpolreg->save();
								$result = DB::insert('polregs', array('customer_id', 'type_id', 'noka', 'nosin', 'tgl_daftar', 'upload_id', 'area_id', 'thn_buat'))
													->values(array($customerid, $typeid, $xl['noka'], $xl['nosin'], $xl['tgl'],$upid, $areaid, $xl['thn']))->execute();
								$polregid= $result[0];									
								$ket = "1";
																	
								//echo $customerid."-".$modelid."-".$merkid."-".$typeid."-".$noka."-".$nosin."-".$tgl_daftar."-".$upload_id."-".$areaid."-".$thn;
							}//dtpolreg
							else 
							{
								$ket = "0";
							}*/
						
						}
						
						$datas[] = array(
											'customer' 	=> $customerid,
											'type'		=> $typeid,
											'noka'		=> $xl['noka'],
											'nosin'		=> $xl['nosin'],
											'tgl'		=> $xl['tgl'],
											'area'		=> $areaid,
											'tahun'		=> $xl['thn'],
											'cc'		=> $xl['cc'],
										);
					}
					$db->commit();
				} 
				catch (Database_Exception $e)
				{
				     // Insert failed. Rolling back changes...
				     $db->rollback();
				     //return false;
				 }
				 
				 return $datas;
				 
				 //return true;
			}
			
			public function create_data($datas, $upid)
			{
				//$datas = array();
				$db = Database::instance();
				$db->begin();
				try 
				{			
					foreach($datas as $data)
					{

								
							if(!ORM::factory('polreg')->where('noka', '=', $data['noka'])->or_where('nosin', '=', $data['nosin'])->find()->id)
							{
								$result = DB::insert('polregs', array('customer_id', 'type_id', 'noka', 'nosin', 'tgl_daftar', 'upload_id', 'area_id', 'thn_buat', 'cc'))
													->values(array($data['customer'], $data['type'], $data['noka'], $data['nosin'], $data['tgl'],$upid, $data['area'], $data['tahun'], $data['cc']))->execute();
								//echo $result[0]."<br/>"	;								
								//echo $customerid."-".$modelid."-".$merkid."-".$typeid."-".$noka."-".$nosin."-".$tgl_daftar."-".$upload_id."-".$areaid."-".$thn;
							}//dtpolreg	
							else 
							{
								$result = DB::update('polregs')->set(array('customer_id'=>$data['customer'], 'type_id'=>$data['type'], 'nosin'=>$data['nosin'], 'tgl_daftar'=>$data['tgl'], 'area_id'=>$data['area'], 'thn_buat'=>$data['tahun'], 'cc'=>$data['cc']))
													->where('noka', '=', $data['noka'])->execute();
/*													echo $data['tgl'];
													echo "upadate <br />";*/
							}			

					}
					$db->commit();
				} 
				catch (Database_Exception $e)
				{
				     // Insert failed. Rolling back changes...
				     $db->rollback();
				     //return false;
				 }			
			}
			
			public function cek_db()
			{
				return DB::select()->from('polregs')->execute();
			}
								
			public function cek_data($nosin, $noka)
			{
				return (bool) DB::select(array('COUNT("*")', 'total_count'))
					->from($this->_table_name)
					->where('nosin', '=', $nosin)
					->where('noka', '=', $noka)
					->execute($this->_db)
					->get('total_count');				
			}
								

}